select CT.CUFOT_VERNUM,
(SELECT I2.USER_ID
-- CT.PAROT_ID, cufot.cuf_id,i2.ista_dtsys,I2.ISS_ID,CT.VPXCO_VAL,I2.*
        
     FROM ista i2
     where i2.az_id=01
     and I2.CSI_ID=ct.csi_id
    AND I2.ISTA_VERSTATO=CT.ISTA_VERSTATO
     AND I2.ISS_STAID=2
     AND I2.ISS_ID=338
     AND I2.BOBJ_NOME='VALPARXCUFOT'
     ) user_id,
(SELECT trunc(I2.ISTA_DT)
-- CT.PAROT_ID, cufot.cuf_id,i2.ista_dtsys,I2.ISS_ID,CT.VPXCO_VAL,I2.*
        
     FROM ista i2
     where i2.az_id=01
     and I2.CSI_ID=ct.csi_id
    AND I2.ISTA_VERSTATO=CT.ISTA_VERSTATO
     AND I2.ISS_STAID=2
     AND I2.ISS_ID=338
     AND I2.BOBJ_NOME='VALPARXCUFOT'
     ) data_att_valore,

C.con_anno,C.CON_ID,TRUNC(C.CON_DTINIGES) DATA_INIZIO_GESTIONE,T.TCON_DES,
C.CON_ANNOSCA,C.CON_GGMMSCA,SOG.SOG_RAGSOC,
C.SOG_ID,UBI.CUF_ID,ATT.ATT_IDEXT POD,OT.OT_DES,
CT.VPXCO_VAL ,TRUNC(CT.VER_DTINI) DATA_INIZIO_PREZZO,
TRUNC(CT.VER_DTFINE) DATA_FINE_PREZZO,ct.parot_id,

(select distinct MAX(T.PAROT_des) from PAROT T WHERE T.AZ_ID=01 AND
T.PAROT_id=ct.parot_id) PAROT_DES,

 (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = CT.AZ_ID
          AND MASTER_ISTA.CSI_ID = CT.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = CT.ISTA_VERSTATO
           ) STA_IDSYS

from contratto c,CONUBIFRN UBI,VALPARXCUFOT CT,CUFOT CTT,TCONTRATTO T,SOGGETTO SOG,ATTACCO ATT,OT

WHERE 
OT.AZ_ID=01
AND OT.OT_ID IN (240,255,524,254,522,241,526,239,281,282,285,523,581)
AND OT.OT_ID=CTT.OT_ID
and   (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = ctt.AZ_ID
          AND MASTER_ISTA.CSI_ID = ctt.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = ctt.ISTA_VERSTATO
           ) =02
and   (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = ot.AZ_ID
          AND MASTER_ISTA.CSI_ID = ot.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = ot.ISTA_VERSTATO
           ) =02
AND
  (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = c.AZ_ID
          AND MASTER_ISTA.CSI_ID = c.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = c.ISTA_VERSTATO
           ) =02
AND (select CASE
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER = 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAID
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER = 0
              THEN
                 master_ista.ISS_STAIDSUPER
              WHEN     master_ista.ISS_STAID <> 0
                   AND master_ista.ISS_STAIDSUPER <> 0
                   AND master_ista.ISS_SSTAIDSUPER <> 0
              THEN
                 master_ista.ISS_SSTAIDSUPER
              ELSE
                 -1
           END 
           from ista master_ista where 
            MASTER_ISTA.AZ_ID = CT.AZ_ID
          AND MASTER_ISTA.CSI_ID = CT.CSI_ID
          AND MASTER_ISTA.ISTA_VERSTATO = CT.ISTA_VERSTATO
           ) =02

AND T.SER_ID=03
AND UBI.AZ_ID=01
AND ATT.AZ_ID=01
AND C.AZ_ID=01
AND SOG.AZ_ID=01
AND T.AZ_ID=01
AND CTT.AZ_ID=01
AND CT.AZ_ID=01
AND C.SOG_ID=SOG.SOG_ID
AND UBI.ATT_ID=ATT.ATT_ID
AND T.TCON_ID=C.TCON_ID
AND C.TCON_ID<>22
AND C.CON_ANNO=UBI.CON_ANNO
AND C.CON_ID=UBI.CON_ID
AND UBI.CUF_ID=CTT.CUF_ID
AND CTT.VER_NUM=CT.CUFOT_VERNUM

AND CT.PAROT_ID in (select distinct T.PAROT_ID from PAROT T WHERE T.AZ_ID=01 AND
T.PAROT_DES LIKE'%FISS%' OR T.PAROT_DES LIKE'%SCON%' OR T.PAROT_DES LIKE'%SBIL%')

AND UBI.CUF_ID IN (SELECT DISTINCT COT.CUF_ID FROM OT,CUFOT COT
WHERE OT.AZ_ID=01
AND COT.AZ_ID=01
AND OT.SER_ID=03
AND OT.COMSER_ID=16
AND OT.OT_ID=COT.OT_ID)
AND CT.VER_DTINI>=to_date(^DATA INIZIO APPLICAZIONE PREZZO >= DI DD/MM/YYYY;A;ALFABETICO^^,'dd/mm/yyyy')
--AND CT.VER_DTFINE<=to_date(^DATA FINE APPLICAZIONE PREZZO <= DI DD/MM/YYYY;A;ALFABETICO^^,'dd/mm/yyyy')

ORDER BY C.con_anno,C.CON_ID
